import json
import pandas as pd
from pathlib import Path

# ==== 文件路径 ====
jsonl_path = Path("/Users/yangchen/Desktop/car_ut/preprocessed_data/all_cases.jsonl")
excel_path = Path("/Users/yangchen/Desktop/HIL测试用例生成/003 G393_BDC_EW01_雨刮洗涤系统功能测试报告V2.xlsx")
output_path = Path("/Users/yangchen/Desktop/car_ut/preprocessed_data/cases4rag.json")

# ==== 读取 JSONL ====
cases_ori = {}
cases_name = {}
with open(jsonl_path, "r", encoding="utf-8") as f:
    for line in f:
        item = json.loads(line.strip())
        case_id = item.get("case_id")
        if case_id:
            cases_ori[case_id] = item.get("original_text", "")
            cases_name[case_id] = item.get("case_name", "")

# ==== 读取 Excel ====
df = pd.read_excel(excel_path,sheet_name=2)

# ==== 按 case_id 分组 ====
merged_data = {}

for case_id, group in df.groupby("需求编号\nRequirement ID"):
    tests = []
    # for case_id, group in df.groupby("case_id"):
    for _, row in group.iterrows():
        tests.append({
            "test_name": row.get("用例名称/描述\nTest Case Name", ""),
            "subsystem": row.get("子系统\nSubsystem", ""),
            "function_module": row.get("功能模块名称\nFunction Module Name", ""),
            "preconditions": row.get("前置条件\nPreconditions", ""),
            "operate_step": row.get("操作步骤\nOperate Step", ""),
            "expected_results": row.get("预期结果\nExpected results", ""),
            "test_environment":"台驾",
            "test_level": row.get("测试等级\nTest Level", "")
        })


    # 获取该 case_id 对应的原始文本
    original_text = cases_ori.get(case_id)
    if original_text:
        merged_data[original_text] = {
            "case_id": case_id,
            "case_name": cases_name.get(case_id, ""),
            "Tests": tests
        }
    else:
        print(f"警告: 未找到 case_id {case_id} 对应的原始文本")

# ==== 输出 ====
with open(output_path, "w", encoding="utf-8") as f:
    json.dump(merged_data, f, indent=2, ensure_ascii=False)

print(f"合并完成{output_path}")
